<html>
 <head>
  <meta charset="UTF-8">
 </head>
 <body>
  <h1 data-lake-id="SWeXW" id="SWeXW"><span data-lake-id="uc5016c5b" id="uc5016c5b">典型回答</span></h1>
  <p data-lake-id="u6a8ce99a" id="u6a8ce99a"><br></p>
  <p data-lake-id="ubdaef588" id="ubdaef588"><span data-lake-id="ucf1111c6" id="ucf1111c6">order by 是做排序的，具体怎么排取决于优化器的选择，如果优化器认为走索引更快，那么就会用索引排序，否则，就会使用filesort (执行计划中extra中提示：using filesort），但是能走索引排序的情况并不多，并且确定性也没有那么强，很多时候，还是走的filesort。</span></p>
  <p data-lake-id="u47b2ea49" id="u47b2ea49"><span data-lake-id="u00ad3c51" id="u00ad3c51">​</span><br></p>
  <p data-lake-id="u6563891b" id="u6563891b"><span data-lake-id="ucf06b8d8" id="ucf06b8d8">filesort这种排序方式中，如果需要排序的内容比较少，就会基于内存中的sort_buffer，否则就需要使用临时文件进行排序了。并且在实际排序过程中，如果字段长度并不是特别长，那么就会使用全字段排序的方式直接在sort_buffer中排序后返回结果集。如果字段长度特别长，那么就可能基于空间考虑，采用row_id排序，这样就会在排序后进行二次回表后返回结果集。</span></p>
  <p data-lake-id="uff3f4bc5" id="uff3f4bc5"><br></p>
  <h2 data-lake-id="nEd7J" id="nEd7J"><span data-lake-id="u8e726ef6" id="u8e726ef6">索引排序</span></h2>
  <p data-lake-id="u200839d7" id="u200839d7"><br></p>
  <p data-lake-id="u4f46b020" id="u4f46b020"><span data-lake-id="ufe0952c5" id="ufe0952c5">我们都知道，索引是天然有序的，所以当我们在使用order by的时候，如果能借助索引，那么效率一定是最高的。</span></p>
  <p data-lake-id="u81737a15" id="u81737a15"><span data-lake-id="ue3c8315c" id="ue3c8315c">​</span><br></p>
  <p data-lake-id="u84d84712" id="u84d84712"><span data-lake-id="udc5cd3a6" id="udc5cd3a6">并且MySQL确实也可以基于索引进行order by的查询，但是这个过程是否一定用索引，完全取决于优化器的选择。</span></p>
  <p data-lake-id="u4d45a895" id="u4d45a895"><span data-lake-id="uff400857" id="uff400857">​</span><br></p>
  <pre lang="java"><code>
 CREATE TABLE `t2` (          
  `id` INT(11),
  `a` varchar(64) NOT NULL,                                                                                                                                                          
  `b` varchar(64) NOT NULL,                                                                                                                                                          
  `c` varchar(64) NOT NULL,                                                                                                                                                          
  `d` varchar(64) NOT NULL,                                                                                                                                                          
  `f` varchar(64) DEFAULT NULL,    
  PRIMARY KEY(id),
  UNIQUE KEY `f` (`f`),                                                                                                                                                              
  KEY `idx_abc` (`a`,`b`,`c`)         
  KEY `idx_a` (`a`)    
) ENGINE=InnoDB DEFAULT CHARSET=latin1
</code></pre>
  <p data-lake-id="u988bb158" id="u988bb158"><br></p>
  <p data-lake-id="u795924a9" id="u795924a9"><span data-lake-id="u95babd54" id="u95babd54">假设有以上这样一张表，在排序时，</span><strong><span data-lake-id="u0b09177e" id="u0b09177e">可能</span></strong><span data-lake-id="uf41f60ed" id="uf41f60ed">出现的情况如下（因为有优化器的干预，以下结果并不一定可以100%复现。我自己实验的时候是可以的，我的环境是MySQL 5.7，）：</span></p>
  <p data-lake-id="ue9f7e2fc" id="ue9f7e2fc"><span data-lake-id="uf8fdabbe" id="uf8fdabbe">​</span><br></p>
  <pre lang="java"><code>
select * from t2 order by a;

-- 不走索引，使用filesort（后面介绍啥是filesort）


select * from t2 order by a limit 100;

-- 走索引


select a,b from t2 order by a limit 100;

-- 走索引


select a,b,c from t2 order by a;

-- 走索引


select a,b,c,d from t2 order by a;

-- 不走索引，使用filesort


select a,b,c,d from t2 where a = "Hollis" order by b;

-- 走索引


select a,b,c,d from t2 where b = "Hollis" order by a;

-- 不走索引，使用filesort

</code></pre>
  <p data-lake-id="uf970c93e" id="uf970c93e"><br></p>
  <p data-lake-id="u2317d79c" id="u2317d79c"><span data-lake-id="u2a99dfb4" id="u2a99dfb4">也就是说，当我们使用索引字段进行排序的时候，优化器会根据成本评估选择是否通过索引进行排序。经过我的不断验证，以下几种情况，走索引的概率很高：</span></p>
  <p data-lake-id="u6eb6b336" id="u6eb6b336"><span data-lake-id="u69612d34" id="u69612d34">​</span><br></p>
  <ul list="uea3cba8a">
   <li fid="u67766539" data-lake-id="u6df71ab9" id="u6df71ab9"><span data-lake-id="uf64b46cb" id="uf64b46cb">查询的字段和order by的字段组成了一个联合索引，并且查询条件符合最左前缀匹配，查询可以用到索引覆盖。如</span><code data-lake-id="u429b9cb0" id="u429b9cb0"><span data-lake-id="u9d898aa0" id="u9d898aa0">select a,b,c from t2 order by a;</span></code></li>
   <li fid="u67766539" data-lake-id="u3d03e5c4" id="u3d03e5c4"><span data-lake-id="u39d45b89" id="u39d45b89">查询条件中有limit，并且limit的数量并不高。（我测试的表数据量80万，在limit超过2W多的时候就不走索引了），如</span><code data-lake-id="ud0a60a7b" id="ud0a60a7b"><span data-lake-id="u8547a105" id="u8547a105">order by a limit 100</span></code></li>
   <li fid="u67766539" data-lake-id="u809eb909" id="u809eb909"><span data-lake-id="ub1684d14" id="ub1684d14">虽然没有遵循最左前缀匹配，但是前导列通过常量进行查询，如</span><code data-lake-id="u04cf33f1" id="u04cf33f1"><span data-lake-id="u535fbf64" id="u535fbf64"> where a = "Hollis" order by b</span></code></li>
  </ul>
  <p data-lake-id="u72ce836c" id="u72ce836c"><br></p>
  <h2 data-lake-id="nE9Ql" id="nE9Ql"><span data-lake-id="uc7181cc9" id="uc7181cc9">filesort 排序</span></h2>
  <p data-lake-id="u46b9cd44" id="u46b9cd44"><br></p>
  <p data-lake-id="ub6d91fe1" id="ub6d91fe1"><span data-lake-id="u50f4e3db" id="u50f4e3db">如果不能使用或者优化器认为索引排序效率不高时， MySQL 会执行filesort操作以读取表中的行并对它们进行排序。</span></p>
  <p data-lake-id="u056bcca7" id="u056bcca7"><span data-lake-id="uac103723" id="uac103723">​</span><br></p>
  <p data-lake-id="uf89fcf5d" id="uf89fcf5d"><span data-lake-id="ua223e4d4" id="ua223e4d4">在进行排序时，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer，它的大小是由</span><code data-lake-id="u46c849d6" id="u46c849d6"><span data-lake-id="u30f0b7b3" id="u30f0b7b3" class="lake-fontsize-12" style="color: rgb(48, 48, 48)">sort_buffer_size</span></code><span data-lake-id="uf17789e6" id="uf17789e6" class="lake-fontsize-12" style="color: rgb(48, 48, 48)">控制的。</span></p>
  <p data-lake-id="uf9c6a12a" id="uf9c6a12a"><span data-lake-id="uaf7a775b" id="uaf7a775b" class="lake-fontsize-12" style="color: rgb(48, 48, 48)">​</span><br></p>
  <p data-lake-id="u460935f6" id="u460935f6"><span data-lake-id="u70b05afd" id="u70b05afd">而根据</span><code data-lake-id="u317800b0" id="u317800b0"><span data-lake-id="ubc8357a7" id="ubc8357a7">sort_buffer_size</span></code><span data-lake-id="u4a8b97c0" id="u4a8b97c0">的大小不同，会在不同的地方进行排序操作：</span></p>
  <ul list="u50637e30">
   <li fid="u521ee546" data-lake-id="u3166e546" id="u3166e546"><span data-lake-id="u8a38bf12" id="u8a38bf12">如果要排序的数据量小于 </span><code data-lake-id="ucbc0e9dc" id="ucbc0e9dc"><span data-lake-id="u4b4846b1" id="u4b4846b1">sort_buffer_size</span></code><span data-lake-id="u7a055b83" id="u7a055b83">，那么排序就在</span><strong><span data-lake-id="ube91107a" id="ube91107a">内存</span></strong><span data-lake-id="u1b4bfa67" id="u1b4bfa67">中完成。</span></li>
   <li fid="u521ee546" data-lake-id="u43cfd2a5" id="u43cfd2a5"><span data-lake-id="udd17d47d" id="udd17d47d">如果排序数据量大于</span><code data-lake-id="ua7cc8dc9" id="ua7cc8dc9"><span data-lake-id="u8bfd5313" id="u8bfd5313">sort_buffer_size</span></code><span data-lake-id="u86174094" id="u86174094">，则需要利用</span><strong><span data-lake-id="u187075d9" id="u187075d9">磁盘临时文件</span></strong><span data-lake-id="ub221f317" id="ub221f317">辅助排序。</span></li>
  </ul>
  <p data-lake-id="u656ceb52" id="u656ceb52"><br></p>
  <blockquote data-lake-id="u616a745a" id="u616a745a">
   <p data-lake-id="u9a288bcc" id="u9a288bcc"><span data-lake-id="uc683a5e7" id="uc683a5e7">临时文件排序采用归并排序的算法，首先会把需要排序的数据拆分到多个临时文件里同步进行排序操作，然后把多个排好序的文件合并成一个结果集返回给客户端。</span></p>
   <p data-lake-id="u30a68f38" id="u30a68f38"><span data-lake-id="udb4ccc8c" id="udb4ccc8c">在磁盘上的临时文件里排序相对于在内存中的sort buffer里排序来说，会慢很多。</span></p>
  </blockquote>
  <p data-lake-id="ub8d40f08" id="ub8d40f08"><span data-lake-id="ubb4b5fc4" id="ubb4b5fc4">​</span><br></p>
  <p data-lake-id="u8eb38688" id="u8eb38688"><span data-lake-id="u9ded0d1e" id="u9ded0d1e">除了</span><code data-lake-id="u3b17f8a7" id="u3b17f8a7"><span data-lake-id="u2461c3f1" id="u2461c3f1">sort_buffer_size</span></code><span data-lake-id="u9361080a" id="u9361080a">参数以外，影响排序的算法的还有一个关键参数：</span><code data-lake-id="ue86590c4" id="ue86590c4"><span data-lake-id="u1455ac96" id="u1455ac96">max_length_for_sort_data</span></code></p>
  <p data-lake-id="u36113083" id="u36113083"><br></p>
  <p data-lake-id="u7e850fbd" id="u7e850fbd"><code data-lake-id="ufd26b1ec" id="ufd26b1ec"><span data-lake-id="uf8690896" id="uf8690896" class="lake-fontsize-12" style="color: rgb(51, 51, 51)">max_length_for_sort_data</span></code><span data-lake-id="u62e94435" id="u62e94435" class="lake-fontsize-12" style="color: rgb(51, 51, 51)">是 MySQL</span><span data-lake-id="ud25ed6f3" id="ud25ed6f3"> 中控制&lt;用于排序的行数据的长度&gt;的一个参数，默认值是1024字节。如果单行的长度超过这个值，MySQL就认为单行太大，那么就会采用</span><strong><span data-lake-id="u9a65246f" id="u9a65246f" class="lake-fontsize-12" style="color: rgb(51, 51, 51)">rowid 排序</span></strong><span data-lake-id="u2848ba05" id="u2848ba05" class="lake-fontsize-12" style="color: rgb(51, 51, 51)">，否则就进行</span><strong><span data-lake-id="u55e95579" id="u55e95579" class="lake-fontsize-12" style="color: rgb(51, 51, 51)">全字段排序</span></strong><span data-lake-id="u8fe028a5" id="u8fe028a5" class="lake-fontsize-12" style="color: rgb(51, 51, 51)">。</span></p>
  <p data-lake-id="uaedcba46" id="uaedcba46"><span data-lake-id="u4f0890f5" id="u4f0890f5" class="lake-fontsize-12" style="color: rgb(51, 51, 51)">​</span><br></p>
  <h3 data-lake-id="G3Hup" id="G3Hup"><span data-lake-id="ufc1a20a4" id="ufc1a20a4" style="color: rgb(51, 51, 51)">全字段排序</span></h3>
  <p data-lake-id="u69f8013c" id="u69f8013c"><br></p>
  <p data-lake-id="udcfc676a" id="udcfc676a"><span data-lake-id="u1f79c511" id="u1f79c511" class="lake-fontsize-12">所谓全字段排序，就是将要查询的所有字段都放到sort_buffer中，然后再根据排序字段进行排序，排好之后直接把结果集返回给客户端。</span></p>
  <p data-lake-id="ucff0965f" id="ucff0965f"><span data-lake-id="uf326838d" id="uf326838d" class="lake-fontsize-12">​</span><br></p>
  <p data-lake-id="uef19b600" id="uef19b600"><span data-lake-id="u7ffd3bc5" id="u7ffd3bc5" class="lake-fontsize-12">假如我们有如下查询SQL：</span></p>
  <p data-lake-id="uc85663aa" id="uc85663aa"><span data-lake-id="u9ecfaec3" id="u9ecfaec3" class="lake-fontsize-12">​</span><br></p>
  <pre lang="java"><code>
select a,d,f from t2 where a = "Hollis" order by d;

-- 
</code></pre>
  <p data-lake-id="ub23341d3" id="ub23341d3"><span data-lake-id="u81672c8f" id="u81672c8f">​</span><br></p>
  <p data-lake-id="ud9f7b4b3" id="ud9f7b4b3"><span data-lake-id="uf4057a59" id="uf4057a59">因为这里涉及到的字段有a,d,f三个，那么就会把符合where条件的所有数据的a,d,f字段都放到sort_buffer中，然后再在sort_buffer中根据d进行排序，排好之后返回给客户端。大致过程如下：</span></p>
  <p data-lake-id="u1fe300ad" id="u1fe300ad"><span data-lake-id="ud46080c7" id="ud46080c7">​</span><br></p>
  <p data-lake-id="u7671bedb" id="u7671bedb"><span data-lake-id="ud73c7a39" id="ud73c7a39">1、从索引a中取出 a = "Hollis"的第一条记录的主键ID</span></p>
  <p data-lake-id="u3ab715e5" id="u3ab715e5"><span data-lake-id="u73e77c92" id="u73e77c92">2、根据主键ID回表，取出a,d,f三个字段，存入sort_buffer</span></p>
  <p data-lake-id="ud0107b8e" id="ud0107b8e"><span data-lake-id="u15cd8352" id="u15cd8352">3、继续查询下一个符合a = "Hollis"的记录，重读第1-2步骤</span></p>
  <p data-lake-id="u487a0f12" id="u487a0f12"><span data-lake-id="u81369e4f" id="u81369e4f">4、在sort_buffer中，根据d进行排序</span></p>
  <p data-lake-id="ubcb2bae9" id="ubcb2bae9"><span data-lake-id="u1d019671" id="u1d019671">5、将排序后的结果集返回给客户端</span></p>
  <p data-lake-id="u82ad6ca3" id="u82ad6ca3"><span data-lake-id="u468aece7" id="u468aece7">​</span><br></p>
  <p data-lake-id="u810d9117" id="u810d9117"><img src="https://cdn.nlark.com/yuque/0/2024/png/5378072/1705327004578-ab52ae7b-14ad-447e-b493-b3e06a7f3d74.png?x-oss-process=image%2Fwatermark%2Ctype_d3F5LW1pY3JvaGVp%2Csize_43%2Ctext_SmF2YSA4IEd1IFA%3D%2Ccolor_FFFFFF%2Cshadow_50%2Ct_80%2Cg_se%2Cx_10%2Cy_10"></p>
  <p data-lake-id="u9696ffcc" id="u9696ffcc"><span data-lake-id="u83ae660f" id="u83ae660f">​</span><br></p>
  <p data-lake-id="ub6f00012" id="ub6f00012"><span data-lake-id="ubd66440f" id="ubd66440f">以上过程中，如果数据sort buffer放不下了，就会采用临时文件，然后再对临时文件进行归并排序。</span></p>
  <p data-lake-id="u1b6b8cda" id="u1b6b8cda"><span data-lake-id="ueb16e64e" id="ueb16e64e">​</span><br></p>
  <p data-lake-id="u4fa6a59b" id="u4fa6a59b"><span data-lake-id="u420b21d2" id="u420b21d2">全字段排序的好处就是只对原表进行了一次回表查询（每条记录只需要回表一次），之后的排序好以后就可以直接吧需要的字段返回了。所以他的效率比较高。但是他的缺点就是，如果要查询的字段比较多，那么就会比较耗费sort_buffer的空间，使得空间中能存储的数据很少。那么如果要排序的数据量变大，就会要用到临时文件，导致整体的性能下降。</span></p>
  <p data-lake-id="uca3327fb" id="uca3327fb"><span data-lake-id="u9b3b0062" id="u9b3b0062">​</span><br></p>
  <p data-lake-id="uf6fc4113" id="uf6fc4113"><span data-lake-id="u0ebb7b32" id="u0ebb7b32">那么，为了避免这个问题，也可以通过row_id排序的方式。</span></p>
  <p data-lake-id="ub8a3d9c6" id="ub8a3d9c6"><span data-lake-id="u5809fdee" id="u5809fdee">​</span><br></p>
  <h3 data-lake-id="zVSjf" id="zVSjf"><span data-lake-id="u6907012e" id="u6907012e">row_id 排序</span></h3>
  <p data-lake-id="u133d645b" id="u133d645b"><br></p>
  <p data-lake-id="u68f2de18" id="u68f2de18"><span data-lake-id="u577ab5cf" id="u577ab5cf">这个也比较容易理解，就是说我们在构建sort_buffer的时候，不要把所有的要查询字段都放进去，只把排序字段这主键放进去就行了。</span></p>
  <p data-lake-id="u247e9686" id="u247e9686"><span data-lake-id="ufb92ee99" id="ufb92ee99">​</span><br></p>
  <pre lang="java"><code>
select a,d,f from t2 where a = "Hollis" order by d;
</code></pre>
  <p data-lake-id="u79043070" id="u79043070"><br></p>
  <p data-lake-id="ua6bb3b4b" id="ua6bb3b4b"><span data-lake-id="u41ee4d07" id="u41ee4d07">比如这个SQL，那么只需要把b和id放到sort_buffer中，先按照d进行排序。排好之后，就在根据id，把对应的a,d,f几个字段都查询出来，返回给客户端即可。大致过程如下：</span></p>
  <p data-lake-id="ube0e4eb7" id="ube0e4eb7"><span data-lake-id="u095e154d" id="u095e154d">​</span><br></p>
  <p data-lake-id="u3dd3a188" id="u3dd3a188"><span data-lake-id="u7ad72bc8" id="u7ad72bc8">1、从索引a中取出 a = "Hollis"的第一条记录的主键ID</span></p>
  <p data-lake-id="uf3eab111" id="uf3eab111"><span data-lake-id="u6fbb47d2" id="u6fbb47d2">2、根据主键ID回表，取出d这一个字段，存入sort_buffer</span></p>
  <p data-lake-id="ub1f99956" id="ub1f99956"><span data-lake-id="u368618a9" id="u368618a9">3、继续查询下一个符合a = "Hollis"的记录，重读第1-2步骤</span></p>
  <p data-lake-id="u9194a5a2" id="u9194a5a2"><span data-lake-id="u1bd36552" id="u1bd36552">4、在sort_buffer中，根据d进行排序</span></p>
  <p data-lake-id="u6092f333" id="u6092f333"><u><span data-lake-id="ua6bfa7d6" id="ua6bfa7d6">5、再根据ID，回表查询出a,d,f几个字段</span></u></p>
  <p data-lake-id="u59123151" id="u59123151"><span data-lake-id="u3bd14af1" id="u3bd14af1">6、将结果集返回给客户端</span></p>
  <p data-lake-id="u6d2683fc" id="u6d2683fc"><span data-lake-id="u8db4b0c7" id="u8db4b0c7">​</span><br></p>
  <p data-lake-id="u0e5c1ddb" id="u0e5c1ddb"><img src="https://cdn.nlark.com/yuque/0/2024/png/5378072/1705327030869-b238aa06-2977-4c3e-a382-183e6ba98fd8.png?x-oss-process=image%2Fwatermark%2Ctype_d3F5LW1pY3JvaGVp%2Csize_42%2Ctext_SmF2YSA4IEd1IFA%3D%2Ccolor_FFFFFF%2Cshadow_50%2Ct_80%2Cg_se%2Cx_10%2Cy_10"></p>
  <p data-lake-id="u03045aea" id="u03045aea"><span data-lake-id="u4e919dac" id="u4e919dac">​</span><br></p>
  <p data-lake-id="ufc34412b" id="ufc34412b"><span data-lake-id="u1311cb7d" id="u1311cb7d">以上的第五步，就是比全字段排序算法中多出来的一步，可以看到，这个方案多了一次回表。所以他的效率肯定也要更慢一些。</span></p>
  <p data-lake-id="u494f7719" id="u494f7719"><span data-lake-id="u13b25c66" id="u13b25c66">​</span><br></p>
  <h3 data-lake-id="YAkaD" id="YAkaD"><span data-lake-id="u90645db5" id="u90645db5">如何选择</span></h3>
  <p data-lake-id="u9f18ac37" id="u9f18ac37"><br></p>
  <p data-lake-id="uea2b51db" id="uea2b51db"><span data-lake-id="ua7a8f7af" id="ua7a8f7af">其实，row_id是MySQL的一种优化算法，他会优先考虑使用全字段排序，只有在他认为字段长度过长，可能会影响效率时，采用row_id的方式排序。并且，能用sort_buffer搞定的情况，MySQL就不会采用临时文件。</span></p>
  <p data-lake-id="ued8ab92e" id="ued8ab92e"><span data-lake-id="u458f4e80" id="u458f4e80">​</span><br></p>
  <p data-lake-id="u97769bef" id="u97769bef"><span data-lake-id="ud98d5d51" id="ud98d5d51">总之就是，速度优先，内存优先、一次回表优先。<br><br></span></p>
 </body>
</html>